0.1 Excel Support for Business Intelligence and Data Analytics

Business Intelligence and Analytics

You have probably heard of business intelligence (BI) and data analytics. Careers in these areas are among those in the highest demand. Two interesting trends are driving this demand.

First, many executives see business intelligence and data analytics as critical for the future success of their business. Second, powerful BI and data analytics tools that once required advanced processing capabilities and specialized training are increasingly available in widely adopted business applications—such as Microsoft Excel. The convergence of increasing demand for business intelligence and data analytics coupled with the increased capacity of personal computer applications for performing BI and data analytics work has led to an explosion of interest in BI.

The definitions of business intelligence (BI) and data analytics are many and varied. To some, business intelligence is the all-encompassing term that combines business analytics, data mining, data visualization, and infrastructure to help organizations make data-driven decisions.1 Thomas Davenport2 argues that business intelligence should be divided into querying, reporting, online analytical processing, an “alerts” tool, and business (data) analytics.3

Business intelligence considers the past, present, and future of an organization and how well that company has performed economically. Business intelligence is also used by managers to make a range of tactical and strategic business decisions that will impact both the near and distant future success of the company.

In this course, we define BI as computer applications that change raw data into significant and meaningful information to help organizations make better decisions. Data are raw, unorganized facts. Information is data that has been analyzed and organized so that it has meaning and is useful for decision-making. Business intelligence activities include reporting on the past or current state of the company, developing insights that help managers to better understand their business, performing optimization analyses, and making future predictions from historical data.

Data analytics is often considered a subset of business intelligence because it focuses on manipulation of the data. Data analytics is focused on understanding existing conditions, developing insights, predicting future conditions, and evaluating various possible future outcomes. The various types of analyses that can be done can be generally categorized into three major types of analytics.

• Descriptive Analytics: Descriptive analytics are those analysis tools and techniques that use data to describe and explain past business performance and current conditions. Descriptive analytics is used to identify and understand those factors which affect business performance. Descriptive analytics is also an important component in the development of models that can be used to make effective business strategy decisions. Descriptive analytics answers such questions as, “What was the revenue by product line this year?” and “What has been the yearly growth over the past three years?”

• Predictive Analytics: Predictive analytics are the tools and techniques that use the information gained from descriptive analytics to predict future performance. Predictive analytics are often expressed in terms of time series, which are forecasted into the future. “What if” analysis is an important technique that is frequently used in predictive analytics. Of course, there are always a myriad of “what if” questions that can be considered. Good descriptive analytics can often help focus the types of predictive analyses that might be the most effective. An example might be, “If fuel costs increased by 10%, what impact would that have on revenues and profits?”

• Prescriptive Analytics: As the name implies, prescriptive analytics is concerned with making effective decisions to optimize future performance. Important techniques in prescriptive analytics are simulation and optimization. These techniques, which will be explained in more detail later, utilize sophisticated tools to evaluate various scenarios to determine effective combinations of input variables to obtain a desired result. This answers such questions as, “What mix of products will maximize income given sales constraints and production constraints?”

Every business is different, and the business environment for every type of business is also different. Data analysis must evaluate how the company is performing in its business environment. Perhaps the primary purpose of data analysis is to understand in depth the current and future health of the organization in the context of the business environment. One important element of this analysis is to define and calculate key performance indicators (KPI). A key performance indicator is a performance measure that accurately indicates the health of an organization. A good KPI can be used to determine if the business is on the right track for a successful future. Usually, more than one KPI will be needed in an organization to accurately measure the health of that organization.

Although KPIs are indicators of organization health, the best KPIs may not always be calculations of revenue or profit. Effective KPIs have the following characteristics:

• They are well-defined and quantifiable.

• They are appropriate for the industry and line of business.

• They are communicated and understood throughout the organization.

• They represent how well business objectives have been achieved.

There are many different types of KPIs that may be important in an organization. Of course, depending on the organization, different types of KPIs will be used. Several various types of KPIs include the following:

• Financial KPIs—revenue growth, revenue per client, profit margin, gross margin

• Sales KPIs—lead conversion rate, sales by product line, sales cycle time, upsell rate

• Marketing KPIs—campaign revenue-to-cost, web traffic, advertising impact, email response rate

• Customer KPIs—customer complaints, unresolved issues, customer satisfaction, repeat customers

• Operational KPIs—order fulfillment time, production cost per unit, inventory turnover rate

• Human Resource KPIs—cost per new hire, revenue per employee, turnover rate, recruiting rate

The data required to calculate a KPI can come from a variety of sources both inside and outside of an organization. Executives use information systems to effectively monitor and interpret the KPIs for their organization. Increasingly, Microsoft Excel is used by managers to identify and then use the KPIs to make well-considered strategic decisions.

Excel Is the Multi-tool of BI

It is fascinating that despite the many sophisticated data analysis tools available to managers, the most used tool is still the ubiquitous and rather long-in-the-tooth tool Excel. In survey after survey, Excel is reported to be the most widely used data analytics tool, even by senior analysts. The reasons for its extensive use can be attributed to its familiarity and ease of use for executives.

Excel is generally the first piece of software that business students learn to use for analyzing data. It quickly becomes the computing version of a jack-of-all-trades that students turn to first to perform any kind of data manipulation. Excel also has intuitive and powerful tools for arranging the layout and presentation of results so that they can be easily shared and communicated to others.

Excel is an ideal platform for working through an analytics problem. It has many of the tools you need for data analysis, involves the analyst in all facets of the analysis process, and can be used to effectively present the results of the analysis. Even though senior analysts have access to refined and powerful tools like R, Python, and Tableau, they still often turn to Excel for data exploration and visualization, data preparation, and preliminary modeling.

You are likely familiar with the Leatherman multi-tool. The Leatherman is a compact device that has many different components including pliers, scissors, screwdrivers, knife blades, and many other useful tools. Although each of these tools is often not as effective as a single-purpose tool, the Leatherman multi-tool is widely used because it can perform so many functions.

Excel is much like the Leatherman multi-tool. It may not be as powerful as many of the special-purpose BI tools for performing a specific analysis, but Excel can be used to perform many different types of data analyses. Additionally, Excel can be used to present the results of an analysis effectively and professionally. Excel is also widely available to almost everybody that does data analysis. It can also be used by people at all skill levels, from beginners to experienced data analysts.

Here are some of the various kinds of analyses that can be performed in Excel:

• What-if analysis

• Sensitivity analysis

• Scenario analysis

• Break-even analysis

• Multidimensional analysis

• Trend analysis

• Optimization (prescriptive analysis)

• Predictive analytics

• Forecasting

• Data preparation

• Regression analysis

• Decision analysis

• Statistical analysis and comparisons

• Simulation

We will learn how to perform these analyses throughout this course.

The purpose of this book is to increase your skill at using Excel as a data analytics tool. However, it is important to note that Excel is not better than R, Python, Tableau, or other specialized data analysis tools. Those tools are often faster, more elegant, and can work on larger datasets. A business intelligence analyst’s main tools for dashboards are Tableau, TIBCO, and Power BI, among several others. A data scientist’s main tools are SQL, Python, and R.4

On the other hand, it is extremely useful to be able to use a multi-tool like Excel to explore data, clean it up, visualize it, and analyze it. Then, after you have determined the analysis you want to perform, you can use a specialized tool like R to complete the analysis. Moreover, after you have an idea of how you wish to present or visualize your data, you can create an elegant dashboard version of your data using Tableau or Power BI. Although Excel may not be the optimal tool for every data analysis situation, it is used in almost all areas of data analytics and BI.

Structure of the Book

This text is structured using a well-known framework for understanding the hierarchy of data analytics.5 In this framework, reporting and analytics activities are visualized along two dimensions. The first is degree of intelligence, which ranges from data (raw facts or figures) to information (organized data that is useful and conveys meaning). The second dimension is the extent to which it may provide competitive advantage (a superior business position) compared to other companies in the same business area.

As we move from left to right in the framework, the BI and data analysis activities and artifacts include:

• Standard reports, which answer the business question “What happened?”

• Ad hoc reports, which help answer an original new question, and OLAP (multidimensional analysis), which helps us drill down, or slice and dice, to discover specifics about the problem.

• Alerts and dashboards, which use KPIs to help managers to see if their organization is on track to meet business goals or if other action is needed.

• Descriptive modeling and statistical analysis, which are used to determine why something is happening.

• Forecasting, which helps managers to understand future trends.

• Predictive modeling, which is used to help managers anticipate the consequences of decisions, such as how customers are likely to behave.

• Optimization, which helps managers choose the best possible solution to a business problem.

In the chart below, we have mapped the book chapters to the framework. Each of the chapters supports one or more of the BI and data analysis activities. Each chapter demonstrates how to perform each activity using Excel.

• Introduction to Data Analysis with Excel—presents an overview of the topics in the book

• Lesson 1: Conditional Aggregate Functions—used in the creation of alerts and dashboards

• Lesson 2: Referencing Functions and Date Functions—used to create standard reports

• Lesson 3: Conditional Formatting Variations—used to create alerts and dashboards

• Lesson 4: Multidimensional Analysis Using PivotTables—used to create ad hoc reports and perform OLAP

• Lesson 5: Advanced Visualization—used to create alerts and dashboards

• Lesson 6: Building Dashboards in Excel—used to create alerts and dashboards

• Lesson 7: Power Query and Power Pivot—used to create ad hoc reports and OLAP

• Lesson 8: Optimization Using Solver in Excel—used to determine the best possible solution

• Lesson 9: Simulation in Excel—used in optimization modeling

• Lesson 10: Exploratory Data Analysis and Basic Probability Distributions—used to create probability distributions as the basis for prediction

• Lesson 11: Inferential Statistics: A/B and A/B/C Tests—used to perform statistical tests on data

• Lesson 12: Prediction Using Linear and Multiple Regression—used to perform forecasting and predictive modeling

• Lesson 13: Predicting Class Using Logistic Regression—used to classify incoming data based on historical data

• Lesson 14: Predicting Class Using the Naïve Bayes Classifier—used to classify categorical data

• Lesson 15: Grouping Objects Using Cluster Analysis—used to find similarities between objects and group them into clusters

• Lesson 16: Forecasting: Time Series—used to perform forecasting and predictive modeling

The book is divided into four sections. The first part of this text covers the use of Excel in business intelligence reporting activities such as using reporting functions, multidimensional analysis, advanced visualization, conditional formatting, dashboards, and some data handling.

The second part of this text covers forecasting through both linear regression and time-series methods.

The third part of this text covers optimization in several situations: decisions in low uncertainty using linear, non-linear, and evolutionary optimization; and decisions in high uncertainty using simulation along with optimization.

The fourth part of this text covers some exploratory and predictive analytic methods of data science, including clustering, market-basket analysis, and text classification, among others.